package cn.yeziji.forum.dao.iquery;

import cn.org.atool.fluent.mybatis.If;
import cn.org.atool.fluent.mybatis.base.crud.IQuery;
import cn.org.atool.fluent.mybatis.base.crud.JoinBuilder;
import cn.yeziji.forum.dto.user.UserFocusOrFansListPagingDTO;
import cn.yeziji.forum.wrapper.*;

import java.util.List;

/**
 * 用户查询构造器
 *
 * @author gzkemays
 * @since 2022/3/28 20:39
 */
public class UserQuery {

  /**
   * 查询粉丝或关注用户列表
   *
   * <p>这里只获取基础的统计信息：昵称 | 头像 | 等级 | 发布的文章数量 | 粉丝数量 | 解答的问题数量
   *
   * @param pagingDTO 分页 dto
   * @return 查询构造器
   */
  public static IQuery selectFocusOrFansUserMsg(UserFocusOrFansListPagingDTO pagingDTO) {
    String[] userIds = pagingDTO.getUserIds();
    return JoinBuilder.from(
            new ForumUserQuery("fu")
                .select
                .id()
                .nickname()
                .avatar()
                .desc()
                .level()
                .focusIds()
                .fansIds()
                .end()
                .select(
                    "IFNULL(fq.questionCount,0) as questionCount",
                    "IFNULL(fa.answerCount,0) as answerCount",
                    "IFNULL(ff.fileCount,0) as fileCount",
                    "IFNULL(fd.detailCount,0) as detailCount")
                .where
                .id()
                .in(userIds)
                .end()
                .groupBy
                .id()
                .end())
        .leftJoin(
            new ForumDetailsQuery()
                .select
                .count
                .id("detailCount")
                .userId()
                .end()
                .where
                .tempSave()
                .isFalse()
                .end()
                .groupBy
                .userId()
                .end(),
            "fd")
        .onApply("fd.userId = fu.id")
        .endJoin()
        .leftJoin(
            new ForumQuestionQuery()
                .select
                .count
                .id("questionCount")
                .userId()
                .end()
                .groupBy
                .userId()
                .end(),
            "fq")
        .onApply("fq.userId = fu.id")
        .endJoin()
        .leftJoin(
            new ForumAnswerQuery()
                .select
                .count
                .id("answerCount")
                .userId()
                .end()
                .groupBy
                .userId()
                .end(),
            "fa")
        .onApply("fa.userId = fu.id")
        .endJoin()
        .leftJoin(
            new ForumFileQuery("ff")
                .select
                .count
                .id("fileCount")
                .source()
                .end()
                .where
                .scope()
                .like("docs/")
                .or
                .scope()
                .like("minio/")
                .end()
                .groupBy
                .source()
                .end(),
            "ff")
        .onApply("ff.source = fu.username")
        .endJoin()
        .build()
        .limit(pagingDTO.prefix(), pagingDTO.last());
  }

  /**
   * 获取用户统计信息
   *
   * <pre>
   *     select
   * 	fu.id,
   * 	fu.nickname,
   * 	fu.`desc`,
   * 	fu.`level`,
   * 	fu.fansIds,
   * 	fu.focusIds,
   * 	fu.createTime,
   * 	IFNULL(count(fd.id),0),
   * 	fq.questionCount,
   * 	fa.answerCount,
   * 	ff.fileCount
   * from
   * 	forum_user fu
   * left join forum_details fd on
   * 	fu.id = fd.userId
   * left join (select count(fq.id) as questionCount, fq.userId from forum_question fq group by fq.userId) as fq on fq.userId = fu.id
   * left join (select count(fa.id) as answerCount, fa.userId from forum_answer fa group by fa.userId) as fa on fa.userId = fu.id
   * left join (select count(ff.id) as fileCount, ff.source from forum_file ff where ff.`scope` like concat('%','docs/','%') or ff.`scope` like concat('%','minio/','%')  group by ff.source) as ff on ff.source = fu.username
   * where
   * 	fu.id = 19;
   * </pre>
   *
   * @param userId 用户 id
   * @param nickname 用户对象昵称
   * @return 查询构造器
   */
  public static IQuery staticsUserMsgVoById(Integer userId, String nickname) {
    return JoinBuilder.from(
            new ForumUserQuery("fu")
                .select
                .id()
                .nickname()
                .avatar()
                .desc()
                .level()
                .fansIds()
                .focusIds()
                .createTime()
                .end()
                .select(
                    "IFNULL(fq.questionCount,0) as questionCount",
                    "IFNULL(fa.answerCount,0) as answerCount",
                    "IFNULL(ff.fileCount,0) as fileCount",
                    "IFNULL(fd.detailCount,0) as detailCount")
                .where
                .id()
                .eq(userId, If::notNull)
                .and
                .nickname()
                .eq(nickname, If::notBlank)
                .end())
        .leftJoin(
            new ForumDetailsQuery()
                .select
                .count
                .id("detailCount")
                .userId()
                .end()
                .where
                .tempSave()
                .isFalse()
                .end()
                .groupBy
                .userId()
                .end(),
            "fd")
        .onApply("fd.userId = fu.id")
        .endJoin()
        .leftJoin(
            new ForumQuestionQuery()
                .select
                .count
                .id("questionCount")
                .userId()
                .end()
                .groupBy
                .userId()
                .end(),
            "fq")
        .onApply("fq.userId = fu.id")
        //        .onEq(ForumUserEntity::getId, ForumQuestionEntity::getUserId)
        .endJoin()
        .leftJoin(
            new ForumAnswerQuery()
                .select
                .count
                .id("answerCount")
                .userId()
                .end()
                .groupBy
                .userId()
                .end(),
            "fa")
        .onApply("fa.userId = fu.id")
        //        .onEq(ForumUserEntity::getId, ForumAnswerEntity::getUserId)
        .endJoin()
        .leftJoin(
            new ForumFileQuery("ff")
                .select
                .count
                .id("fileCount")
                .source()
                .end()
                .where
                .scope()
                .like("docs/")
                .or
                .scope()
                .like("minio/")
                .end()
                .groupBy
                .source()
                .end(),
            "ff")
        .onApply("ff.source = fu.username")
        //        .onEq(ForumUserEntity::getUsername, ForumFileEntity::getSource)
        .endJoin()
        .build();
  }

  public static ForumUserQuery selectUserMsgByIds(List<Integer> ids) {
    return new ForumUserQuery().where.id().in(ids).end();
  }

  /**
   * 根据电子邮箱来获取用户
   *
   * @param email 电子邮箱
   * @return 查询构造器
   */
  public static ForumUserQuery selectUserByEmail(String email) {
    return new ForumUserQuery().where.email().eq(email).end();
  }

  /**
   * 登录查询
   *
   * @param username 用户名
   * @param password 密码
   * @return 查询构造器
   */
  public static ForumUserQuery selectUserByLogin(String username, String password) {
    return new ForumUserQuery().where.username().eq(username).and.password().eq(password).end();
  }
  /**
   * 根据用户名来获取用户
   *
   * @param username 用户名
   * @return 查询构造器
   */
  public static ForumUserQuery selectUserByUserNameQuery(String username) {
    return new ForumUserQuery().where.username().eq(username).end();
  }
  /**
   * 根据用户名来获取用户
   *
   * @param nickname 用户昵称
   * @return 查询构造器
   */
  public static ForumUserQuery selectUserByNickNameQuery(String nickname) {
    return new ForumUserQuery().where.nickname().eq(nickname).end();
  }
  /**
   * 根据nickname查询用户
   *
   * @param nickname 用户昵称
   * @return 查询构造器
   */
  public static ForumUserQuery selectUserByNickNameQuery(String nickname, Integer userId) {
    return new ForumUserQuery().where.nickname().eq(nickname).and.id().ne(userId).end();
  }

  /**
   * 根据 username 列表查询数据
   * @param usernames 用户名 list
   * @return 查询构造器
   */
  public static ForumUserQuery selectUserByUserNames(List<String> usernames) {
    return new ForumUserQuery().where.username().in(usernames).end();
  }


  /**
   * 根据 nickname 列表查询数据
   * @param nicknames 用户名 list
   * @return 查询构造器
   */
  public static ForumUserQuery selectUserByNickNames(List<String> nicknames) {
    return new ForumUserQuery().where.nickname().in(nicknames).end();
  }


  /**
   * 根据 nickname 或者其他用户 id 获取用户列表
   * @param nickname 用户昵称
   * @param userId 用户 id
   * @return 查询构造器
   */
  public static ForumUserQuery selectUserByNickNameOrOtherUserId(String nickname, Integer userId) {
    return new ForumUserQuery().where.nickname().eq(nickname).or.id().eq(userId).end();
  }
}
